<?php
/*****************************************************

  Copyright:	(c) 2006  SeaDesign Web Studio
        Web:	www.seadesign.ru
      Email: 	seadesign@gmail.com
  
*****************************************************/


require_once 'dbinit.inc';

require_once 'db_pdo.inc';

$db = new Database("mysql:host=$host;dbname=$db", $user, $pass, $charset);


define('INVALID_ID',0);

/*
	%d Day of the month, numeric (00..31) 
	%e Day of the month, numeric (0..31) 
	%m Month, numeric (00..12)
	%Y Year, numeric, four digits 
	
	%H Hour (00..23) 
	%h Hour (01..12) 
	%i Minutes, numeric (00..59) 
	%s Seconds (00..59) 
*/


function dbDateFormat($fnm, $fmt) { 			//oracle: TO_CHAR(fnm, 'DD-MON-YYYY HH:MI:SS')
	return "DATE_FORMAT($fnm,'$fmt')";
}

function dbDateCurrent() {							//oracle: SYSDATE
	return 'CURRENT_TIMESTAMP';
}

function dbDummyTable() {
	return 'DUAL';
}



class Command 
	extends Query 
{
	function __construct($db,$tnm) 
	{
		$this->tnm = $tnm;
		$this->fields = array();
		$this->where = array();
		$this->whereStr = '';
		
		parent::__construct($db);
	}
	
	public function where($fnm, $val) 
	{
		$this->where[$fnm] = $val;
	}
	
	
	public function addWhereString($where) 
	{
		if (!empty($where)) {
			if (!empty($this->whereStr)) {
				$this->whereStr .= ' AND ';
			}
			$this->whereStr .= $where;
		}
	}
	
	public function addWhereArray($where) 
	{
		foreach ($where as $fnm => $val) {
			$this->where($fnm, $val);
		}
	}
	
	public function hasFilterBy($fnm) {
		return isset($this->where[$fnm]);
	}
	
	

	protected function createWhereString()
	{
		$where = '';
		
		foreach ($this->where as $nm => $v) 
		{
			$val = '';
			if (is_array($v)) 
			{
				foreach ($v as $vName) {
					if (is_null($vName)) {
						$val .= (empty($val) ? "$nm IS NULL" : " OR $nm IS NULL");
					}
					else {
						$vName = $this->prepareInputValue($vName);
						$val .= (empty($val) ? "$nm=$vName" : " OR $nm=$vName");
					}
				}
				$val = "($val)";
			}
			else if (is_null($v)) {
				$val = "$nm IS NULL";
			}
			else {
				$v = $this->prepareInputValue($v);
				$val = "$nm=$v";
			}
			$where .= (empty($where) ? $val : " AND $val");
		}
		
		if (!empty($this->whereStr)) {
			if (!empty($where)) {
				$where .= ' AND ';
			}
			$where .= $this->whereStr;
		}
		
		return $where;
	}
		
	protected function prepareInputValue(&$v) 
	{
		static $system = array('CURRENT_TIMESTAMP');
		
		if (is_null($v)) {
			return 'NULL';
		}
		else if (!is_numeric($v) && !in_array($v,$system)) {
			return $this->db->quote($v);
		}
		return $v;
	}

	protected $tnm;
	protected $fields;
	protected $where;
	protected $whereStr;
};



define ('INNER_JOIN', 'INNER JOIN');
define ('LEFT_JOIN',  'LEFT OUTER JOIN');
define ('RIGHT_JOIN', 'RIGHT OUTER JOIN');

define('ASC','ASC');
define('DESC','DESC');


class Select 
	extends Command
{
	function __construct($db, $tnm) 
	{
		$this->joins = array();
		$this->order = array();
		$this->skip = 0;
		$this->limit = 0;
		
		parent::__construct($db,$tnm);
	}
	
	public function addFields() 	//assume multiple params
	{				
		$numargs = func_num_args();
		for ($i=0; $i<$numargs; ++$i) {
			$fnm = func_get_arg($i);
			array_push($this->fields, $fnm);
		}
		return count($this->fields);
	}
	
	public function innerJoin($tnm, $foreignkeynm, $keynm) 
	{
		array_push(
			$this->joins,
			array($tnm, $foreignkeynm, $keynm, INNER_JOIN)
		);
	}
	
	public function leftJoin($tnm, $foreignkeynm, $keynm) 
	{
		array_push(
			$this->joins,
			array($tnm, $foreignkeynm, $keynm, LEFT_JOIN)
		);
	}
	
	public function rightJoin($tnm, $foreignkeynm, $keynm) 
	{
		array_push(
			$this->joins,
			array($tnm, $foreignkeynm, $keynm, RIGHT_JOIN)
		);
	}
	
	public function order($fnm, $dir) 
	{
		$this->order[$fnm] = $dir;
	}
	
	public function hasOrderBy($fnm) 
	{
		return isset($this->order[$fnm]);
	}
	
	public function getOrderBy($fnm) 
	{
		return $this->order[$fnm];
	}
	
	public function setSkip($skip) {		//skip first $skip records
		$this->skip = $skip;
	}
	
	public function setLimit($limit) {		//loads only $limit first records
		$this->limit = $limit;
	}
	
	public function run() 
	{
		$fields = '';
		if (!empty($this->fields)) {
			foreach ($this->fields as $fnm) {
				$fields .= (empty($fields) ? $fnm : ",$fnm");
			}
		} 
		
		$joins = '';
		foreach ($this->joins as $join) {
			$joins .= "$join[3] $join[0] ON $join[1]=$join[2] ";
		}
		
		$where = $this->createWhereString();
		
		$order = '';		
		foreach ($this->order as $nm => $v) {
			$order .= (empty($order) ? "$nm $v" : ",$nm $v");
		}

		$sql = "SELECT $fields FROM $this->tnm $joins";
		
		if (!empty($where)) {
			$sql .= "WHERE $where";
		}
			
		if (!empty($order)) {
			$sql .= " ORDER BY $order";
		}

		if ($this->skip || $this->limit) {
			$sql .= " LIMIT $this->skip,";
			$sql .= ($this->limit ? $this->limit : '18446744073709551615');
		}
			
		$this->exec($sql);
		return $sql;
	}
	

	private $joins;
	private $order;
	private $skip;		//0-based (skip $startFrom records: LIMIT 5,10 - Retrieve rows 6-15)
	private $limit;
};



class Update 
	extends Command
{
	function __construct($db,$tnm) 
	{
		parent::__construct($db,$tnm);
	}
	
	public function addField($fnm, $fval) 
	{
		$this->fields[$fnm] = $fval;
	}
	
	public function addFields($fields) 
	{
		foreach ($fields as $fnm => $fval) 
		{
			$this->addField($fnm, $fval);
		}
	}
	
	public function run() 
	{
		$fields = ''; 
		foreach ($this->fields as $fnm => $fval) {
			$fval = $this->prepareInputValue( $fval );
			$fields .= (empty($fields) ? "$fnm=$fval" : ",$fnm=$fval");
		}

		$where = $this->createWhereString();		
		
		$sql = "UPDATE $this->tnm SET $fields";
		if (!empty($where)) {
			$sql .= " WHERE $where";
		}
		 	
		$this->exec($sql);
		return $sql;
	}
};



class Insert 
	extends Command
{
	function __construct($db,$tnm) {
		parent::__construct($db,$tnm);
	}
	
	public function addField($fnm, $fval) {
		$this->fields[$fnm] = $fval;
	}
	
	public function addFields($fields) {
		foreach ($fields as $fnm => $fval) {
			$this->addField($fnm, $fval);
		}
	}
	
	public function where($fnm, $val) {
		die('No WHERE clause allowed in INSERT SQL statement');
	}
	
	public function addWhereString($where) {
		die('No WHERE clause allowed in INSERT SQL statement');
	}
	
	public function run() 
	{
		$fields = ''; 
		$vals = '';
		foreach ($this->fields as $fnm => $fval) {
			$fields .= (empty($fields) ? $fnm : ",$fnm");			
			$fval = $this->prepareInputValue($fval);
			$vals .= (empty($vals) ? $fval : ",$fval");
		}
		
		$sql = "INSERT INTO $this->tnm ($fields) VALUES($vals)";
		$this->exec($sql);
		return $sql;
	}
};

class Delete 
	extends Command
{
	function __construct($db,$tnm) 
	{
		parent::__construct($db,$tnm);
	}
	
	public function run() 
	{
		$where = $this->createWhereString();
		$sql = "DELETE FROM $this->tnm WHERE $where";
		$this->exec($sql);
		return $sql;
	}
};


/*
function createQuery($sql)
{
	global $db;
	$q = new Query($db);
	$q->prepare($sql);

	if (func_num_args() >= 2)
	{
		$phs = array();
		preg_match_all('/:([a-z_]+)/i', $sql, $phs, PREG_PATTERN_ORDER);
		$phs = $phs[0];
		if (count($phs) != func_num_args()-1)
		{
			$args = func_get_args();
			throw new Exception("SQL placeholders don't match passed params (".implode(',',$args).")");
		}
		for ($i=1; $i<func_num_args(); ++$i)
		{
			$param = func_get_arg($i);
			$q->setParam($phs[$i-1], $param);
		}
		$q->execute();
	}

	return $q;
}
*/


function createQuery($sql, $params=array())
{
	global $db;
	$q = new Query($db);
	$q->prepare($sql);

	foreach ($params as $n => $v) {
		$q->setParam($n, $v);
	}
	
	return $q;
}

function createStaticQuery(&$q, $sql, $params=array())
{
   if (is_null($q)) {
      $q = createQuery($sql);
   }
}


function execQuery($sql, $params=array())
{
	global $db;
	
	if (empty($params)) 
	{
		$q = new Query($db);
		$q->exec($sql);
		return $q->processedCount();
	}
	$q = createQuery($sql, $params);
	$q->execute();
	return $q->processedCount();
}

/** obsolete, use DB::countRecords */
function countRecords($tnm, $where=array()) 
{
	global $db;
	$q = new Select($db, $tnm);
	$q->addFields('COUNT(*)');
	$q->addWhereArray($where);
	$q->run();
	$q->fetch();
	$count = $q->at(0);
	$q->close();
	return $count;
}



//works for MYSQL only
function limitSelect($sql, $limit, $skip=0)
{
	if (!is_null($skip) || !is_null($limit))
	{
		if (is_null($skip)) {
			$skip = 0;
		}
		$sql .= " LIMIT $skip,";
		$sql .= ($limit ? $limit : '18446744073709551615');
	}
	return $sql;
}


/**
	all records
	@param $query   SQL query or iQuery ancestor object prepared and executed
*/
function query2arrays($query, $params=array())
{
	$records = array();
	$q = null;
	
	if (is_string($query))
	{
		$q = createQuery($query, $params);
		$q->execute();
	}
	else
	{
		$q = $query;
	}
	
	while ($q->fetch(DB_FETCH_ASSOC))
	{
		$fields = array();
		foreach ($q as $fname => $fval)
		{
			$fields[$fname] = $fval;
		}
		$records[] = $fields;
	}
	$q->close();
	
	return $records;
}


/**
	one (first) record
	@param $query   SQL query or iQuery ancestor object prepared and executed
*/
function query2array($query, $params=array())
{
	$fields = array();
	$q = null;
	
	if (is_string($query))
	{
		$q = createQuery($query, $params);
		$q->execute();
	}
	else
	{
		$q = $query;
	}
	
	if ($q->fetch(DB_FETCH_ASSOC))
	{
		foreach ($q as $fname => $fval)
		{
			$fields[$fname] = $fval;
		}
	}
	$q->close();
	
	return $fields;
}

/**
	put first field of every record to array
	@param $query   SQL query or iQuery ancestor object prepared and executed
*/
function query2vector($query, $params=array())
{
	$vector = array();
	$q = null;
	
	if (is_string($query))
	{
		$q = createQuery($query, $params);
		$q->execute();
	}
	else
	{
		$q = $query;
	}
	
	while ($q->fetch(DB_FETCH_NUM))
	{
		$vector[] = $q->at(0);
	}
	$q->close();
	
	return $vector;
}


function query2var($sql, $params=array())
{
	$val = false;
	
	$q = createQuery($sql, $params);
	$q->execute();
	if ($q->fetch()) {
		$val = $q->at(0);
	}
	$q->close();
	return $val;
}


class DB
{
   static public function countRecords($sql, $params=array())
   {
	  $sql = preg_replace('/[\n\r]/','',$sql);
	  $sql = preg_replace('/LIMIT [0-9,]*/i', '', $sql);
      $sql = preg_replace('/[ ]*ORDER BY .+$/i','', $sql);
	  
      return query2var("SELECT COUNT(*) FROM ($sql) AS t", $params);
   }
};


?>